-- **************************************************************************
--    Project Name:   客户渠道-客户流向重量段需求
--    Job Name:       jms_dm.dm_customer_province_bill_amount_month_dt
--    Author :        季修魁
--    date：          2023/06/10
-- **************************************************************************
-- **************************************************************************
---月明细统计

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions=1000;
set hive.exec.max.dynamic.partitions.pernode=1000;

with tab_customer_province_month_amount_dt as (
select
    t1.pick_network_code
    ,t1.pick_network_name
    ,t1.first_franchisee_code
    ,t1.first_franchisee_name
    ,t1.send_financial_center_code
    ,t1.send_financial_center_name
    ,case when max(date_time) = '{{ execution_date | cst_ds }}' then '{{ execution_date | cst_ds }}' else last_day(t1.date_time)  end  as  date_time1
    ,last_day(t1.date_time)   as m_end_date
    ,t2.provider_id                  as  pick_network_province_id
    ,case when t2.provider_id = t1.receiver_province_id then 1 else 0 end  as if_in_province_bill
    ,t1.customer_code
    ,t1.customer_name
    ,t1.receiver_province_id
    ,t1.receiver_province_name
    ,sum(t1.total_bill_count            )  as    total_bill_count
    ,sum(t1.total_weight_count          )  as    total_weight_count
    ,sum(t1.total_freight               )  as    total_freight
    ,sum(t1.total_transfer_fee          )  as    total_transfer_fee
    ,sum(t1.total_operation_fee         )  as    total_operation_fee
    ,sum(t1.total_base_dispatch_fee     )  as    total_base_dispatch_fee
    ,sum(t1.total_piece_fee             )  as    total_piece_fee
    ,sum(t1.total_sales_price           )  as    total_sales_price
    ,sum(t1.total_bill_amount           )  as    total_bill_amount
    ,sum(t1.total_prime_cost            )  as    total_prime_cost
    ,sum(t1.total_profit                )  as    total_profit
    ,sum(t1.zero_bill_count             )  as    zero_bill_count
    ,sum(t1.zero_weight                 )  as    zero_weight
    ,sum(t1.zero_freight                )  as    zero_freight
    ,sum(t1.zero_prime_cost             )  as    zero_prime_cost
    ,sum(t1.zero_profit                 )  as    zero_profit
    ,sum(t1.one_bill_count              )  as    one_bill_count
    ,sum(t1.one_weight                  )  as    one_weight
    ,sum(t1.one_freight                 )  as    one_freight
    ,sum(t1.one_prime_cost              )  as    one_prime_cost
    ,sum(t1.one_profit                  )  as    one_profit
    ,sum(t1.two_bill_count              )  as    two_bill_count
    ,sum(t1.two_weight                  )  as    two_weight
    ,sum(t1.two_freight                 )  as    two_freight
    ,sum(t1.two_prime_cost              )  as    two_prime_cost
    ,sum(t1.two_profit                  )  as    two_profit
    ,sum(t1.three_bill_count            )  as    three_bill_count
    ,sum(t1.three_weight                )  as    three_weight
    ,sum(t1.three_freight               )  as    three_freight
    ,sum(t1.three_prime_cost            )  as    three_prime_cost
    ,sum(t1.three_profit                )  as    three_profit
    ,sum(t1.four_bill_count             )  as    four_bill_count
    ,sum(t1.four_weight                 )  as    four_weight
    ,sum(t1.four_freight                )  as    four_freight
    ,sum(t1.four_prime_cost             )  as    four_prime_cost
    ,sum(t1.four_profit                 )  as    four_profit
    ,sum(t1.five_bill_count             )  as    five_bill_count
    ,sum(t1.five_weight                 )  as    five_weight
    ,sum(t1.five_freight                )  as    five_freight
    ,sum(t1.five_prime_cost             )  as    five_prime_cost
    ,sum(t1.five_profit                 )  as    five_profit
    ,sum(t1.six_bill_count              )  as    six_bill_count
    ,sum(t1.six_weight                  )  as    six_weight
    ,sum(t1.six_freight                 )  as    six_freight
    ,sum(t1.six_prime_cost              )  as    six_prime_cost
    ,sum(t1.six_profit                  )  as    six_profit
    ,sum(t1.seven_bill_count            )  as    seven_bill_count
    ,sum(t1.seven_weight                )  as    seven_weight
    ,sum(t1.seven_freight               )  as    seven_freight
    ,sum(t1.seven_prime_cost            )  as    seven_prime_cost
    ,sum(t1.seven_profit                )  as    seven_profit
    ,sum(t1.eight_bill_count            )  as    eight_bill_count
    ,sum(t1.eight_weight                )  as    eight_weight
    ,sum(t1.eight_freight               )  as    eight_freight
    ,sum(t1.eight_prime_cost            )  as    eight_prime_cost
    ,sum(t1.eight_profit                )  as    eight_profit
    ,sum(t1.nine_bill_count             )  as    nine_bill_count
    ,sum(t1.nine_weight                 )  as    nine_weight
    ,sum(t1.nine_freight                )  as    nine_freight
    ,sum(t1.nine_prime_cost             )  as    nine_prime_cost
    ,sum(t1.nine_profit                 )  as    nine_profit
    ,sum(t1.ten_bill_count              )  as    ten_bill_count
    ,sum(t1.ten_weight                  )  as    ten_weight
    ,sum(t1.ten_freight                 )  as    ten_freight
    ,sum(t1.ten_prime_cost              )  as    ten_prime_cost
    ,sum(t1.ten_profit                  )  as    ten_profit
    ,sum(t1.total_with_freight          )  as    total_with_freight
    ,sum(t1.total_without_freight       )  as    total_without_freight
    ,trunc(date_time,'MM')                 as    m_start_date
 from jms_dm.dm_customer_province_bill_amount_dt t1
 left join jms_dim.dim_network_whole_massage           t2
   on t1.pick_network_code = t2.code
where t1.dt>=add_months(trunc('{{ execution_date | cst_ds }}','MM'),-2)
  and t1.dt<='{{ execution_date | cst_ds }}'
group by
    t1.pick_network_code
    ,t1.pick_network_name
    ,t1.first_franchisee_code
    ,t1.first_franchisee_name
    ,t1.send_financial_center_code
    ,t1.send_financial_center_name
    ,t2.provider_id
    ,last_day(t1.date_time)
    ,t1.customer_code
    ,t1.customer_name
    ,t1.receiver_province_id
    ,t1.receiver_province_name
    ,trunc(date_time,'MM')
),

---客户等级

yl_oms_oms_cust_m_lvl as (
select customer_code,m_start_date,m_end_date,max_m_date,pick_network_code
       ,sum(total_bill_count)          as total_bill_count
       ,sum(total_bill_count) / m_cnt  as m_avg_total_bill_count
       ,case when (sum(total_bill_count) / max(m_cnt)) >5000 then 'A'
             when (sum(total_bill_count) / max(m_cnt)) >1000 and (sum(total_bill_count) / max(m_cnt)) <=5000 then 'B'
             when (sum(total_bill_count) / max(m_cnt)) >200  and (sum(total_bill_count) / max(m_cnt)) <=1000 then  'C'
             when (sum(total_bill_count) / max(m_cnt)) >50  and (sum(total_bill_count) / max(m_cnt)) <=200  then 'D'
             when (sum(total_bill_count) / max(m_cnt)) >=0   and (sum(total_bill_count) / max(m_cnt)) <=50  then 'E'
        end as cust_level
  from
(
select customer_code
       ,trunc(date_time,'MM')                 as    m_start_date
       ,last_day(date_time)   as m_end_date
       ,pick_network_code
       ,case when max(date_time) <> last_day(date_time)  then max(date_time) else last_day(date_time)  end        as max_m_date
       ,day(case when max(date_time) <> last_day(date_time)  then max(date_time) else last_day(date_time) end)    as m_cnt
       ,sum(total_bill_count)              as total_bill_count
  from jms_dm.dm_customer_province_bill_amount_dt
 where dt>=add_months(trunc('{{ execution_date | cst_ds }}','MM'),-2) and dt<='{{ execution_date | cst_ds }}'
 group by customer_code,trunc(date_time,'MM'),last_day(date_time),pick_network_code
) t1
group by customer_code,m_start_date,m_end_date,max_m_date,m_cnt,pick_network_code
),


---上月
yl_oms_oms_cust_m_lvl_2 as (
select customer_code,m_start_date,m_end_date,max_m_date,pick_network_code
       ,sum(total_bill_count)          as total_bill_count
       ,sum(total_bill_count) / m_cnt  as m_avg_total_bill_count
       ,case when (sum(total_bill_count) / max(m_cnt)) >5000 then 'A'
             when (sum(total_bill_count) / max(m_cnt)) >1000 and (sum(total_bill_count) / max(m_cnt)) <=5000 then 'B'
             when (sum(total_bill_count) / max(m_cnt)) >200  and (sum(total_bill_count) / max(m_cnt)) <=1000 then  'C'
             when (sum(total_bill_count) / max(m_cnt)) >50  and (sum(total_bill_count) / max(m_cnt)) <=200  then 'D'
             when (sum(total_bill_count) / max(m_cnt)) >=0   and (sum(total_bill_count) / max(m_cnt)) <=50  then 'E'
        end as cust_level
  from
(
select customer_code
       ,trunc(date_time,'MM')                 as    m_start_date
       ,last_day(date_time)   as m_end_date
       ,pick_network_code
       ,case when max(date_time) <> last_day(date_time)  then max(date_time) else last_day(date_time)  end        as max_m_date
       ,day(case when max(date_time) <> last_day(date_time)  then max(date_time) else last_day(date_time) end)    as m_cnt
       ,sum(total_bill_count)              as total_bill_count
  from jms_dm.dm_customer_province_bill_amount_dt
 where dt>=add_months(trunc('{{ execution_date | cst_ds }}','MM'),-3) and dt<=add_months('{{ execution_date | cst_ds }}',-1)
 group by customer_code,trunc(date_time,'MM'),last_day(date_time),pick_network_code
) t1
group by customer_code,m_start_date,m_end_date,max_m_date,m_cnt,pick_network_code
)

insert overwrite table jms_dm.dm_customer_province_bill_amount_month_dt partition(dt)
select
    A.pick_network_code
    ,pick_network_name
    ,first_franchisee_code
    ,first_franchisee_name
    ,send_financial_center_code
    ,send_financial_center_name
    ,trunc(date_time1,'MM')  as date_time
    ,a.customer_code
    ,customer_name
    ,receiver_province_id
    ,receiver_province_name
    ,sum(a.total_bill_count)                             as              total_bill_count
    ,sum(total_weight_count)                             as              total_weight_count
    ,sum(total_weight_count)/sum(a.total_bill_count)       as              total_average_weight
    ,sum(total_freight)                                  as              total_freight
    ,sum(total_transfer_fee)                             as              total_transfer_fee
    ,sum(total_operation_fee)                            as              total_operation_fee
    ,sum(total_base_dispatch_fee)                        as              total_base_dispatch_fee
    ,sum(total_piece_fee)                                as              total_piece_fee
    ,sum(total_sales_price)                              as              total_sales_price
    ,sum(total_bill_amount)                              as              total_bill_amount
    ,sum(total_prime_cost)                               as              total_prime_cost
    ,sum(total_profit)                                   as              total_profit
    ,sum(total_profit)/sum(a.total_bill_count)             as              total_average_profit

    ,sum(total_prime_cost)/sum(a.total_bill_count)         as              total_average_prime
    ,sum(zero_bill_count)                                as              zero_bill_count
    ,sum(zero_weight)                                    as              zero_weight
    ,sum(zero_weight)/sum(zero_bill_count)               as              zero_average_weight
    ,sum(zero_freight)                                   as              zero_freight
    ,sum(zero_prime_cost)                                as              zero_prime_cost
    ,sum(zero_profit)                                    as              zero_profit
    ,sum(zero_profit)/sum(zero_bill_count)               as              zero_average_profit
    ,sum(one_bill_count)                                 as              one_bill_count
    ,sum(one_weight)                                     as              one_weight
    ,sum(one_weight)/sum(one_bill_count)                 as              one_average_weight
    ,sum(one_freight)                                    as              one_freight
    ,sum(one_prime_cost)                                 as              one_prime_cost
    ,sum(one_profit)                                     as              one_profit
    ,sum(one_profit)/sum(one_bill_count)                 as              one_average_profit
    ,sum(two_bill_count)                                 as              two_bill_count
    ,sum(two_weight)                                     as              two_weight
    ,sum(two_weight)/sum(two_bill_count)                 as              two_average_weight
    ,sum(two_freight)                                    as              two_freight
    ,sum(two_prime_cost)                                 as              two_prime_cost
    ,sum(two_profit)                                     as              two_profit
    ,sum(two_profit)/sum(two_bill_count)                 as              two_average_profit
    ,sum(three_bill_count)                               as              three_bill_count
    ,sum(three_weight)                                   as              three_weight
    ,sum(three_weight)/sum(three_bill_count)             as              three_average_weight
    ,sum(three_freight)                                  as              three_freight
    ,sum(three_prime_cost)                               as              three_prime_cost
    ,sum(three_profit)                                   as              three_profit
    ,sum(three_profit)/sum(three_bill_count)             as              three_average_profit
    ,sum(four_bill_count)                                as              four_bill_count
    ,sum(four_weight)                                    as              four_weight
    ,sum(four_weight)/sum(four_bill_count)               as              four_average_weight
    ,sum(four_freight)                                   as              four_freight
    ,sum(four_prime_cost)                                as              four_prime_cost
    ,sum(four_profit)                                    as              four_profit
    ,sum(four_profit)/sum(four_bill_count)               as              four_average_profit
    ,sum(five_bill_count)                                as              five_bill_count
    ,sum(five_weight)                                    as              five_weight
    ,sum(five_weight)/sum(five_bill_count)               as              five_average_weight
    ,sum(five_freight)                                   as              five_freight
    ,sum(five_prime_cost)                                as              five_prime_cost
    ,sum(five_profit)                                    as              five_profit
    ,sum(five_profit)/sum(five_bill_count)               as              five_average_profit
    ,sum(six_bill_count)                                 as              six_bill_count
    ,sum(six_weight)                                     as              six_weight
    ,sum(six_weight)/sum(six_bill_count)                 as              six_average_weight
    ,sum(six_freight)                                    as              six_freight
    ,sum(six_prime_cost)                                 as              six_prime_cost
    ,sum(six_profit)                                     as              six_profit
    ,sum(six_profit)/sum(six_bill_count)                 as              six_average_profit
    ,sum(seven_bill_count)                               as              seven_bill_count
    ,sum(seven_weight)                                   as              seven_weight
    ,sum(seven_weight)/sum(seven_bill_count)             as              seven_average_weight
    ,sum(seven_freight)                                  as              seven_freight
    ,sum(seven_prime_cost)                               as              seven_prime_cost
    ,sum(seven_profit)                                   as              seven_profit
    ,sum(seven_profit)/sum(seven_bill_count)             as              seven_average_profit
    ,sum(eight_bill_count)                               as              eight_bill_count
    ,sum(eight_weight)                                   as              eight_weight
    ,sum(eight_weight)/sum(eight_bill_count)             as              eight_average_weight
    ,sum(eight_freight)                                  as              eight_freight
    ,sum(eight_prime_cost)                               as              eight_prime_cost
    ,sum(eight_profit)                                   as              eight_profit
    ,sum(eight_profit)/sum(eight_bill_count)             as              eight_average_profit
    ,sum(nine_bill_count)                                as              nine_bill_count
    ,sum(nine_weight)                                    as              nine_weight
    ,sum(nine_weight)/sum(nine_bill_count)               as              nine_average_weight
    ,sum(nine_freight)                                   as              nine_freight
    ,sum(nine_prime_cost)                                as              nine_prime_cost
    ,sum(nine_profit)                                    as              nine_profit
    ,sum(nine_profit)/sum(nine_bill_count)               as              nine_average_profit
    ,sum(ten_bill_count)                                 as              ten_bill_count
    ,sum(ten_weight)                                     as              ten_weight
    ,sum(ten_weight)/sum(ten_bill_count)                 as              ten_average_weight
    ,sum(ten_freight)                                    as              ten_freight
    ,sum(ten_prime_cost)                                 as              ten_prime_cost
    ,sum(ten_profit)                                     as              ten_profit
    ,sum(ten_profit)/sum(ten_bill_count)                 as              ten_average_profit
    ,sum(a.total_bill_count)/day(to_date(date_time1))      as            m_avg_total_bill_count
    ,if_in_province_bill
    ,sum(total_with_freight)                             as              total_with_freight
    ,sum(total_without_freight)                          as              total_without_freight
    ,b.cust_level                                        as              cust_level
    ,c.cust_level                                        as              lm_cust_level
    ,trunc(date_time1,'MM')                              as              dt
  from tab_customer_province_month_amount_dt  a
  left join yl_oms_oms_cust_m_lvl             b
    on a.customer_code = b.customer_code
   and a.m_start_date = b.m_start_date
   and a.pick_network_code = b.pick_network_code
 left join yl_oms_oms_cust_m_lvl              C
    on a.customer_code = C.customer_code
   and a.m_start_date = add_months(c.m_start_date,1)
   and a.pick_network_code = C.pick_network_code
  group by
      a.pick_network_code
      ,pick_network_name
      ,first_franchisee_code
      ,first_franchisee_name
      ,send_financial_center_code
      ,send_financial_center_name
      ,date_time1
      ,a.customer_code
      ,customer_name
      ,receiver_province_id
      ,receiver_province_name
      ,if_in_province_bill
      ,b.cust_level,c.cust_level
  distribute by dt,pmod(hash(customer_code), 10)
  ;